﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

namespace WSDatos
{
    public class clsFacturas
    {
        //Si cve_cte es 0, arroja todas las facturas de la cia en cuestión, si no arroja las del cliente que se especifica
        public DataTable ObtenerFacturas(int cve_cia, int cve_cte)
        {
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";
            if (cve_cte == 0)
            {
                comando = "SELECT HF.FEC_FAC AS FECHA_FACTURACION, HF.SUB_TOT AS SUBTOTAL, HF.IVA AS IVA, " +
                "HF.NETO AS TOTAL, HF.FEC_EMB AS FECHA_EMBARCACION, C.NOMBRE AS NOMBRE_CLIENTE, T.RAZON AS TRANSPORTE " +
                "FROM CLIENTES C " +
                "INNER JOIN H_FACTS HF ON C.CVE_CTE = HF.CVE_CTE " +
                "INNER JOIN MONEDAS MON ON HF.CVE_MON = MON.CVE_MON " +
                "INNER JOIN TRANSPOR T ON C.CVE_TRA = T.CVE_TRA " +
                "WHERE HF.CVE_CIA = " + cve_cia;
            }
            else
            {
                comando = "SELECT HF.FEC_FAC AS FECHA_FACTURACION, HF.SUB_TOT AS SUBTOTAL, HF.IVA AS IVA, " +
                "HF.NETO AS TOTAL, HF.FEC_EMB AS FECHA_EMBARCACION, T.RAZON AS TRANSPORTE " +
                "FROM CLIENTES C " +
                "INNER JOIN H_FACTS HF ON C.CVE_CTE = HF.CVE_CTE " +
                "INNER JOIN MONEDAS MON ON HF.CVE_MON = MON.CVE_MON " +
                "INNER JOIN TRANSPOR T ON C.CVE_TRA = T.CVE_TRA " +
                "WHERE HF.CVE_CIA = " + cve_cia + " AND HF.CVE_CTE = " + cve_cte;
            }
            dtt = manejoInfo.FncSelecciona(comando, "TodasFacturas");
            return dtt;
        }

        //Si cve_cte es 0, arroja todas las facturas de la cia en cuestión, si no arroja las del cliente que se especifica
        public DataTable ObtenerFacturasPorFecha(int cve_cia, int cve_cte, string fechaInicial, string fechaFinal)
        {
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";
            if (cve_cte == 0)
            {
                comando = "SELECT HF.CVE_FAC AS CLAVE, HF.FEC_FAC AS FECHA_FACTURACION, HF.SUB_TOT AS SUBTOTAL, HF.IVA AS IVA, " +
                "HF.NETO AS TOTAL, HF.FEC_EMB AS FECHA_EMBARCACION, C.NOMBRE AS NOMBRE_CLIENTE, T.RAZON AS TRANSPORTE " +
                "FROM CLIENTES C " +
                "INNER JOIN H_FACTS HF ON C.CVE_CTE = HF.CVE_CTE " +
                "INNER JOIN MONEDAS MON ON HF.CVE_MON = MON.CVE_MON " +
                "INNER JOIN TRANSPOR T ON C.CVE_TRA = T.CVE_TRA " +
                "WHERE HF.CVE_CIA = " + cve_cia +
                "AND FEC_FAC >= '" + fechaInicial + "' AND FEC_FAC <= '" + fechaFinal + "'";
            }
            else
            {
                comando = "SELECT HF.CVE_FAC AS CLAVE, HF.FEC_FAC AS FECHA_FACTURACION, HF.SUB_TOT AS SUBTOTAL, HF.IVA AS IVA, " +
                "HF.NETO AS TOTAL, HF.FEC_EMB AS FECHA_EMBARCACION, T.RAZON AS TRANSPORTE " +
                "FROM CLIENTES C " +
                "INNER JOIN H_FACTS HF ON C.CVE_CTE = HF.CVE_CTE " +
                "INNER JOIN MONEDAS MON ON HF.CVE_MON = MON.CVE_MON " +
                "INNER JOIN TRANSPOR T ON C.CVE_TRA = T.CVE_TRA " +
                "WHERE HF.CVE_CIA = " + cve_cia + " AND HF.CVE_CTE = " + cve_cte + " " +
                "AND FEC_FAC >= '" + fechaInicial + "' AND FEC_FAC <= '" + fechaFinal + "'";
            }
            dtt = manejoInfo.FncSelecciona(comando, "TodasFacturas");
            return dtt;
        }

        //Si cve_cte es 0, arroja todas las facturas de la cia en cuestión, si no arroja las del cliente que se especifica
        public DataTable ObtenerFacturasCanceladas(int cve_cia, int cve_cte)
        {
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";
            if (cve_cte == 0)
            {
                comando = "SELECT HF.FEC_FAC AS FECHA_FACTURACION, HF.SUB_TOT AS SUBTOTAL, HF.IVA AS IVA, " +
                "HF.NETO AS TOTAL, HF.FEC_EMB AS FECHA_EMBARCACION, C.NOMBRE AS NOMBRE_CLIENTE, T.RAZON AS TRANSPORTE " +
                "FROM CLIENTES C " +
                "INNER JOIN H_FACTS HF ON C.CVE_CTE = HF.CVE_CTE " +
                "INNER JOIN MONEDAS MON ON HF.CVE_MON = MON.CVE_MON " +
                "INNER JOIN TRANSPOR T ON C.CVE_TRA = T.CVE_TRA " +
                "WHERE HF.CVE_CIA = " + cve_cia +
                "AND STATUS = 'C'";
            }
            else
            {
                comando = "SELECT HF.FEC_FAC AS FECHA_FACTURACION, HF.SUB_TOT AS SUBTOTAL, HF.IVA AS IVA, " +
                "HF.NETO AS TOTAL, HF.FEC_EMB AS FECHA_EMBARCACION, T.RAZON AS TRANSPORTE " +
                "FROM CLIENTES C " +
                "INNER JOIN H_FACTS HF ON C.CVE_CTE = HF.CVE_CTE " +
                "INNER JOIN MONEDAS MON ON HF.CVE_MON = MON.CVE_MON " +
                "INNER JOIN TRANSPOR T ON C.CVE_TRA = T.CVE_TRA " +
                "WHERE HF.CVE_CIA = " + cve_cia + " AND HF.CVE_CTE = " + cve_cte + " " +
                "AND STATUS = 'C'";
            }
            dtt = manejoInfo.FncSelecciona(comando, "TodasFacturas");
            return dtt;
        }

        //Si cve_cte es 0, arroja todas las facturas de la cia en cuestión, si no arroja las del cliente que se especifica
        public DataTable ObtenerFacturasActivas(int cve_cia, int cve_cte)
        {
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";
            if (cve_cte == 0)
            {
                comando = "SELECT HF.FEC_FAC AS FECHA_FACTURACION, HF.SUB_TOT AS SUBTOTAL, HF.IVA AS IVA, " +
                "HF.NETO AS TOTAL, HF.FEC_EMB AS FECHA_EMBARCACION, C.NOMBRE AS NOMBRE_CLIENTE, T.RAZON AS TRANSPORTE " +
                "FROM CLIENTES C " +
                "INNER JOIN H_FACTS HF ON C.CVE_CTE = HF.CVE_CTE " +
                "INNER JOIN MONEDAS MON ON HF.CVE_MON = MON.CVE_MON " +
                "INNER JOIN TRANSPOR T ON C.CVE_TRA = T.CVE_TRA " +
                "WHERE HF.CVE_CIA = " + cve_cia +
                "AND STATUS = 'F'";
            }
            else
            {
                comando = "SELECT HF.FEC_FAC AS FECHA_FACTURACION, HF.SUB_TOT AS SUBTOTAL, HF.IVA AS IVA, " +
                "HF.NETO AS TOTAL, HF.FEC_EMB AS FECHA_EMBARCACION, T.RAZON AS TRANSPORTE " +
                "FROM CLIENTES C " +
                "INNER JOIN H_FACTS HF ON C.CVE_CTE = HF.CVE_CTE " +
                "INNER JOIN MONEDAS MON ON HF.CVE_MON = MON.CVE_MON " +
                "INNER JOIN TRANSPOR T ON C.CVE_TRA = T.CVE_TRA " +
                "WHERE HF.CVE_CIA = " + cve_cia + " AND HF.CVE_CTE = " + cve_cte + " " +
                "AND STATUS = 'F'";
            }
            dtt = manejoInfo.FncSelecciona(comando, "TodasFacturas");
            return dtt;
        }

        //Arrojo el detalle de una factura
        public DataTable ObtenerDetalleFactura(int cve_fac)
        {
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";
            
            comando = "SELECT CVE_PED, CVE_NUM, PARES FROM D_FACTS " +
            "WHERE CVE_FAC = " + cve_fac;
            
            dtt = manejoInfo.FncSelecciona(comando, "DetalleFactura");
            return dtt;
        }        
    }
}